package Fisica.Toner;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Fisica.Conexion;
import Inteface.InterfaceFisica;
import Modelo.Toner;

public class DAOToner implements InterfaceFisica{
	public static String FIRST = "SELECT * FROM `toner` order by `id` asc limit 1";
	public static String LAST = "SELECT * FROM `toner` order by `id` desc limit 1";
	public static String PRE = "SELECT * FROM `toner` WHERE `id` < ? order by `id` desc";
	public static String POST = "SELECT * FROM `toner` WHERE `id` > ? order by `id` asc";
	
	public boolean insert(Object obj) { //ok
		Toner v = (Toner)obj;

		String SELECT = "select * from `toner` "
		+ "where `marca` = '"+v.getMarca()+"' "
			+ "and `modelo` = '"+v.getModelo()+"';";

		/* INSERT INTO `dbtoner`.`toner` 
		 * (`id`, `codigo`, `marca`, `modelo`) 
		 * VALUES ('1', 'A1234', 'HP', 'A12'), ('2', 'Q5978', 'HP', '643A');
		 * 
		 * INSERT INTO `toner` (`marca`, `modelo`, `codigo`) VALUES ("HP", "A12","A1234");
		 * */
//		String INSERTAR = "INSERT INTO `toner` (`marca`, `modelo`, `codigo`, `stock`, `urlImg`, `pesoImg`) "
//		+ "VALUES ('"+v.getMarca().toUpperCase()+"', '"+v.getModelo().toUpperCase()+"', '"+v.getCodigo().toUpperCase()+"', "+v.getStock()+");";

				String INSERTAR = "INSERT INTO `toner` (`marca`, `modelo`, `codigo`, `stock`, `urlImg`, `pesoImg`) "
				+ "VALUES (?,?,?,?,?,?);";
//		String INSERTAR = "INSERT INTO `toner` (`marca`, `modelo`, `codigo`, `stock`, `urlImg`) "  //OK
//				+ "VALUES (?,?,?,?,?);";
		


		try {	
			FileInputStream archivofoto; //para convertirlo a bytes.
			File file = new File(v.getUrlImg());
			PreparedStatement st;
			ResultSet rs;
			
			//RECUPERO
			st = Conexion.getConexion().prepareStatement(SELECT);
			rs = st.executeQuery();
			if(rs.next()){
				v.setId(rs.getInt("id"));
				System.out.println("ya existe: ID "+v.getId());
			}
			else{
				//INSERTO
				st = Conexion.getConexion().prepareStatement(INSERTAR);	
				st.setString(1, v.getMarca());
				st.setString(2, v.getModelo());
				st.setString(3, v.getCodigo());
				st.setInt(4, v.getStock());
				st.setString(5, v.getUrlImg());
				archivofoto = new FileInputStream(v.getUrlImg());
				System.out.println("archivo: "+archivofoto);
				st.setBinaryStream(6, archivofoto, (int)file.length());
//				st.setBinaryStream(6, archivofoto);
							
				st.executeUpdate();
				
				System.out.println("inserto: "+v.getId());
			}		

			return true;
			
		}catch(Exception e){
			System.out.println("exception al intentar insertar: "+v.getId());
			e.printStackTrace();
		}
		return false;
			
	}
	
	public boolean update(Object obj){ //ok
		Toner v = (Toner)obj;

		try {	
			
		String SELECT = "select * from `toner` "
					+ "where `marca` = '"+v.getMarca()+"' "
						+ "and `modelo` = '"+v.getModelo()+"';";
					
		FileInputStream archivofoto = new FileInputStream(v.getUrlImg());
					
		String UPDATE = "UPDATE `Toner` SET "
				+ "`marca` = '"+v.getMarca().toUpperCase()+"', "
				+ "`modelo` = '"+v.getModelo().toUpperCase()+"', "
				+ "`codigo` ='"+v.getCodigo().toUpperCase()+"', "
				+ "`urlImg` = '"+v.getUrlImg()+"', "
				+ "`pesoImg` = '"+archivofoto+"' "
				+ "WHERE `id` = '"+v.getId()+"';";
		
					
			PreparedStatement st;
			ResultSet rs;
			//INSERTO
			st = Conexion.getConexion().prepareStatement(UPDATE);				
			st.executeUpdate();
			//RECUPERO
			st = Conexion.getConexion().prepareStatement(SELECT);
			rs = st.executeQuery();
			if(rs.next())
				v.setId(rs.getInt("id"));
			System.out.println("actualizo: "+v.getId());

			return true;
			
		}catch(Exception e){
			System.out.println("exception al intentar insertar: "+v.getId());
			e.printStackTrace();
		}
		return false;			
	}
	
	public boolean stock(Object obj){ //ok
		Toner v = (Toner)obj;

		String SELECT = "select * from `toner` "
		+ "where `marca` = '"+v.getMarca()+"' "
			+ "and `modelo` = '"+v.getModelo()+"';";

		String UPDATE = "UPDATE `Toner` SET `stock` = "+v.getStock()+" WHERE `id` = '"+v.getId()+"';";


		try {			
			PreparedStatement st;
			ResultSet rs;
			//INSERTO
			st = Conexion.getConexion().prepareStatement(UPDATE);				
			st.executeUpdate();
			//RECUPERO
			st = Conexion.getConexion().prepareStatement(SELECT);
			rs = st.executeQuery();
			if(rs.next())
				v.setId(rs.getInt("id"));
			System.out.println("actualizo: "+v.getId());

			return true;
			
		}catch(Exception e){
			System.out.println("exception al intentar insertar: "+v.getId());
			e.printStackTrace();
		}
		return false;			
	}
	
	
	public boolean delete(Object obj){ //ok
		Toner v = (Toner)obj;
		String delete = "delete from `toner` where `id` = '"+v.getId()+"';";
		String select = "select * from `toner` where `id` = '"+v.getId()+"';";
		try {			
			PreparedStatement st;
			ResultSet rs;
			st = Conexion.getConexion().prepareStatement(delete);	
			st.executeUpdate();
			
			st = Conexion.getConexion().prepareStatement(select);	
			rs = st.executeQuery();
			
			System.out.println("inserto: "+v.getId());

			return !rs.next();
			
		}catch(Exception e){
			System.out.println("exception al intentar insertar: "+v.getId());
			e.printStackTrace();
		}
		return false;
	}
	
	public List<Object> list (){ //ok
		List<Object> lista = new ArrayList();	
		String sql = "select * from `toner`;";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			ResultSet rs = st.executeQuery();			
			
			while (rs.next()){
				Toner o = new Toner(); 
				o.setId(rs.getInt("id")); 				
				o.setMarca(rs.getString("marca"));				
				o.setModelo(rs.getString("modelo"));					
				o.setCodigo(rs.getString("codigo"));			 	
				o.setStock(rs.getInt("stock"));
				o.setUrlImg(rs.getString("urlImg"));
				o.setImg((FileInputStream) (rs.getBlob("pesoImg")).getBinaryStream());
				lista.add(o);
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return lista;		
	}
	
	public List<Object> listMarcas (){ //ok
		List<Object> lista = new ArrayList();	
		String sql = "select * from `toner` group by `marca`;";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			ResultSet rs = st.executeQuery();			
			
			while (rs.next()){
				Toner o = new Toner(); 
				o.setId(rs.getInt("id")); 				
				o.setMarca(rs.getString("marca"));				
				o.setModelo(rs.getString("modelo"));					
				o.setCodigo(rs.getString("codigo"));			 	
				
				lista.add(o);
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return lista;		
	}
	
	public List<Object> listMarca (String marca){ //ok
		List<Object> lista = new ArrayList();	
		String sql = "select * from `toner` where `marca` = ? group by `modelo`;";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			st.setString(1, marca);
			ResultSet rs = st.executeQuery();			
			
			while (rs.next()){
				Toner o = new Toner(); 
				o.setId(rs.getInt("id")); 				
				o.setMarca(rs.getString("marca"));				
				o.setModelo(rs.getString("modelo"));					
				o.setCodigo(rs.getString("codigo"));			 	
				
				lista.add(o);
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return lista;		
	}

	public Object getObject (PreparedStatement st){ //ok
		Toner o = new Toner(); 	
		
		try {
//			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			ResultSet rs = st.executeQuery();			
			FileOutputStream archivofoto;
			File file; 
			if (rs.next()){
				o.setId(rs.getInt("id")); 				
				o.setMarca(rs.getString("marca"));				
				o.setModelo(rs.getString("modelo"));					
				o.setCodigo(rs.getString("codigo"));
				o.setStock(rs.getInt("stock"));
				o.setUrlImg(rs.getString("urlImg"));
				
				file = new File(o.getUrlImg());
				archivofoto = new FileOutputStream(file);      
//				archivofoto = new FileOutputStream(o.getUrlImg());
				Blob bin = rs.getBlob("pesoImg");
				InputStream inStream = bin.getBinaryStream();
                int size = (int)bin.length();
                byte[] buffer = new byte[size];
                int length = -1;
                while ((length = inStream.read(buffer)) != -1)
                {
                	archivofoto.write(buffer, 0, length);                
                } 
                
				o.setImg( (FileInputStream) inStream);
			}	
			return o;
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			
			e.printStackTrace();
		} catch (IOException e) {
			
			e.printStackTrace();
		} 		
		return new Toner();		
	}
	
	@Override
	public Object getPre(int id) {
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(PRE);
			st.setInt(1, id);
			return getObject(st);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public Object getPost(int id) {
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(POST);
			st.setInt(1, id);
			return getObject(st);
		} catch (SQLException e) {			
			e.printStackTrace();
		}		
		return null;
	}

	@Override
	public Object getFirst() {
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(FIRST);
			return getObject(st);
		} catch (SQLException e) {			
			e.printStackTrace();
		}		
		return null;
	}

	@Override
	public Object getLast() {
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(LAST);
			return getObject(st);			
		} catch (SQLException e) {			
			e.printStackTrace();
		}		
		return null;
	}
	
	public Object getById(int id){ //ok
		String sql = "select * from `toner` where `id` = "+id+";";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			ResultSet rs = st.executeQuery();			
			
			if (rs.next()){
				Toner o = new Toner(); 
				o.setId(rs.getInt("id")); 				
				o.setMarca(rs.getString("marca"));				
				o.setModelo(rs.getString("modelo"));					
				o.setCodigo(rs.getString("codigo"));	
				o.setStock(rs.getInt("stock"));
				return o;
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return new Toner();	
	}
	
	public Object getStock(int id){ //ok
		String sql = "select * from `toner` where `id` = "+id+";";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);	
			ResultSet rs = st.executeQuery();			
			
			if (rs.next()){
				Toner o = new Toner(); 
				o.setId(rs.getInt("id")); 				
				o.setMarca(rs.getString("marca"));				
				o.setModelo(rs.getString("modelo"));					
				o.setCodigo(rs.getString("codigo"));	
				o.setStock(rs.getInt("stock"));
				return o;
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return new Toner();	
	}
	
	public Object getByMarcaModelo(String marca, String modelo){ //ok
		String sql = "select * from `toner` where `marca` = ? and `modelo` = ?;";
		try {
			PreparedStatement st = Conexion.getConexion().prepareStatement(sql);
			st.setString(1, marca);
			st.setString(2, modelo);
			ResultSet rs = st.executeQuery();			
			
			if (rs.next()){
				Toner o = new Toner(); 
				o.setId(rs.getInt("id")); 				
				o.setMarca(rs.getString("marca"));				
				o.setModelo(rs.getString("modelo"));					
				o.setCodigo(rs.getString("codigo"));
				o.setStock(rs.getInt("stock"));
				return o;
				}			
		} catch (SQLException e) {
			System.out.print("ERROR EN LA RECUPERACION DE DATOS DE LA BASE");
			e.printStackTrace();
		} 		
		return new Toner();	
	}

	
	

	public static void main(String[] args) {
		Toner t = new Toner();
		t.setModelo("A12");
		t.setMarca("HP");
		t.setCodigo("A123564");
		DAOToner daot = new DAOToner();
//		daot.insert(t); //ok
		Toner t2 = (Toner) daot.getById(1); //ok
		System.out.println("marca: "+t2.getMarca());
		t2.setMarca("epson");
//		if (daot.update(t2)) //ok
//			System.out.println("EXITO");
//		else
//			System.out.println("FRACASO");
		
		List<Object> l = daot.list(); // ok
		Toner y;
		for(Object x: l){
			y = (Toner)x;
			System.out.println("id: "+y.getId());
			System.out.println("marca: "+y.getMarca());
			System.out.println("modelo: "+y.getModelo());
			System.out.println("codigo: "+y.getCodigo());
		}
		
		if (daot.delete(t2)) //ok
			System.out.println("EXITO");
		else
			System.out.println("FRACASO");
		
		l = daot.list(); // ok
		for(Object x: l){
			y = (Toner)x;
			System.out.println("id: "+y.getId());
			System.out.println("marca: "+y.getMarca());
			System.out.println("modelo: "+y.getModelo());
			System.out.println("codigo: "+y.getCodigo());
		}
	}

	@Override
	public boolean exist(Object obj) { //ok
		Toner v = (Toner)obj;

		String SELECT = "select * from `toner` "
		+ "where `marca` = '"+v.getMarca()+"' "
			+ "and `modelo` = '"+v.getModelo()+"';";

		try {			
			PreparedStatement st;
			ResultSet rs;
			//RECUPERO
			st = Conexion.getConexion().prepareStatement(SELECT);
			rs = st.executeQuery();
			if(rs.next())
				v.setId(rs.getInt("id"));
			System.out.println("existe: "+v.getId());

			return true;
			
		}catch(Exception e){
			System.out.println("exception al intentar insertar: "+v.getId());
			e.printStackTrace();
		}
		return false;
			
	}

	/*
	 *  public static void RecuperarBLOB
    (Connection cn,  String idBLOB, String path)
    throws SQLException, IOException
    {
        FileOutputStream fos = null;
        Statement st = null;
        ResultSet rs = null;
        String sql ="select CO_ARCHIVO, " +
                    "       NOMBRE_ARCHIVO, " + 
                    "       BIN, " + 
                    "       FX_ALTA " + 
                    "from archivos " + 
                    "WHERE CO_ARCHIVO = '" + idBLOB + "' ";
      
        try{
            st = cn.createStatement();        
            rs = st.executeQuery(sql);
            if (rs.next()) 
            {                
                String pathname= 
                path + "\\" + rs.getString("NOMBRE_ARCHIVO") ;
                File file = new File(pathname);
                fos = new FileOutputStream(file);                    
                Blob bin = rs.getBlob("BIN");
                InputStream inStream = bin.getBinaryStream();
                int size = (int)bin.length();
                byte[] buffer = new byte[size];
                int length = -1;
                while ((length = inStream.read(buffer)) != -1)
                {
                  fos.write(buffer, 0, length);                
                }                                        
            }
        }
        catch (IOException ioe)
        {
            throw new IOException(ioe.getMessage());
        }
        finally 
        {
            if (fos != null)
                fos.close();
            if (rs != null)
                rs.close();
            rs = null;
            st = null;        
        }        
    }
}*/

	
}
